异构数据源访问

配置HDFS数据源

前提条件

• 自建Hadoop集群或具有Hadoop-Common、HDFS和YARN服务的E-MapReduce集群需与AnalyticDB PostgreSQL版实例在同一VPC。

• 已将AnalyticDB PostgreSQL版实例的专有网络交换机IP添加至Hadoop集群所在的安全组。为确保AnalyticDB PostgreSQL版与Hadoop间的网络端口畅通，需将以下异构数据源访问功能会用到的Hadoop服务默认的端口全部加入安全组。具体操作，请参见添加安全组规则。

  服务	端口号
  Namenode IPC	Hadoop2.x版本：8020 Hadoop3.x版本：9820 E-MapReduce集群：9000 具体端口号请参见配置文件hdfs-site.xml -> dfs.namenode.rpc-address中的端口号。
  DataNode IPC	Hadoop2.x版本：50020 Hadoop3.x版本：9867 具体端口号请参见配置文件hdfs-site.xml -> dfs.datanode.ipc.address中的端口号。
  DataNode	Hadoop2.x版本：50010 Hadoop3.x版本：9866 具体端口号请参见配置文件hdfs-site.xml -> dfs.datanode.address中的端口号。
  Hive metastore	9083 具体端口号请参见配置文件hive-site.xml -> hive.metastore.uris中的端口号。
  Hive HiveServer	10000 具体端口号请参见配置文件hive-site.xml -> hive.server2.thrift.port中的端口号。
  Zookeeper	2181 具体端口号请参见配置文件zoo.cfg -> clientPort=xxxx中的端口号。
  Kerberos Server	88（传输加密和认证） 和749（kadmin） 具体端口号请参见配置文kdc.conf -> kdc_ports和kdc.conf -> kadmind_port中的端口号。
  JDBC（MySQL或PostgreSQL）	3306、1521、5432等
  ICMP协议	用于网络连通性校验，在安全组规则的入方向与出方向放行ICMP协议。

  说明

  AnalyticDB PostgreSQL版实例的专有网络交换机IP可在外部数据源管理页面获取。

准备测试数据集

准备测试数据集，并执行hadoop fs -put <本地文件名称> <文件保存在Hadoop集群的路径>命令将数据集保存在Hadoop集群路径。

例如，将本地文件/home/user/file.txt上传到HDFS的/tmp目录下，执行hadoop fs -put /home/user/file.txt /tmp/file.tx即可。测试数据集内容如下：

1,test1,1,100
2,test2,2,90

操作步骤

1. 在异构数据源访问页签，单击新增数据源的下拉菜单，选择Hadoop数据源。

2. 在新增Hadoop数据源页面页面依次配置网络&安全组、配置文件、初始化。

   1. 配置网络&安全组。

      参数	说明
      数据源名称	可包含英文大小写字母、数字、中划线（-）、下划线（_）。 以数字或者英文字母开始和结尾。 长度不超过50字符。
      数据源描述	数据源描述。
      数据源类型	当目标数据源为E-MapReduce集群，选择阿里云E-MapReduce。 当目标数据源为自建Hadoop集群，选择自建Hadoop。
      数据库类型	选择HDFS。
      数据来源	E-MapReduce集群ID。 说明 自建Hadoop集群无此参数。
      网络校验	单击网络校验，提示网络连通。

   2. 单击下一步，填写配置文件。

      参数	说明	获取方式
      参数配置	无	若目标数据源为E-MapReduce集群，支持自动配置获取。 若目标数据源为自建Hadoop集群需要自行粘贴配置文件信息。
      hdfs-site.xml	HDFS配置项，如副本因子和块大小等。	自建Hadoop集群xml文件一般位于以下两个位置之一： conf目录：在早期版本的Hadoop中，这些配置文件通常放在Hadoop安装目录下的conf子目录中。 etc/hadoop目录：在较新版本的Hadoop中，配置文件通常放在 etc/hadoop目录下。这个目录位于 Hadoop的主安装目录下。
      core-site.xml	Hadoop核心配置项，如I/O设置和文件系统的配置等。
      yarn-site.xml	YARN配置项，负责集群资源管理和作业调度等。
      mapred-site.xml	用于配置MapReduce作业的参数，如作业执行器和任务优化参数等。
      /etc/host文件	解析Hadoop集群中每个节点的主机名到IP。	有以下两种获取方式： 方式一：依次登录Hadoop集群的每台机器，获取/etc/hosts文件中的本节点IP和主机名的映射关系并填写。 方式二：登录Hadoop namenode所在的机器，运行#!/bin/bash脚本，填写返回结果。返回结果如下： echo $(hostname -i) $(hostname -f) hdfs dfsadmin -report | awk -F':' '/^Name:/ {printf $2} /^Hostname:/ {print $2}'

   3. 单击保存，并单击左下角完成。

      等待3~5分钟，数据源状态为运行中时，HDFS数据源可用。

3. 单击目标数据源操作列的校验。

4. 在服务配置校验窗口中，填写数据集所在Hadoop集群路径（例如：/tmp/file.txt），并单击校验。

   提示配置校验成功后，您可以继续操作读写HDFS外表。

配置Hive数据源

前提条件

• 自建Hive集群或具有Hadoop-Common、HDFS、Hive和YARN服务的E-MapReduce集群需与AnalyticDB PostgreSQL版实例在同一VPC。

• 已将AnalyticDB PostgreSQL版实例的专有网络交换机IP添加至Hive集群所在的安全组。具体操作，请参见添加安全组规则。

  说明

  AnalyticDB PostgreSQL版实例的专有网络交换机IP可在外部数据源管理页面获取。

准备测试数据集

准备测试数据集，并执行hadoop fs -put <本地文件名称> <文件保存在Hadoop集群的路径>命令将数据集保存在Hadoop集群路径。

例如，将本地文件/home/user/file.txt上传到Hive的/tmp目录下，执行hadoop fs -put /home/user/file.txt /tmp/file.tx即可。测试数据集内容如下：

1,test1,1,100
2,test2,2,90

操作步骤

1. 在异构数据源访问页签，单击新增数据源的下拉菜单，选择Hadoop数据源。

2. 在新增Hadoop数据源页面页面依次配置网络&安全组、配置文件、初始化。

   1. 配置网络&安全组。

      参数	说明
      数据源名称	可包含英文大小写字母、数字、中划线（-）、下划线（_）。 以数字或者英文字母开始和结尾。 长度不超过50字符。
      数据源描述	数据源描述。
      数据源类型	当目标数据源为E-MapReduce集群，选择阿里云E-MapReduce。 当目标数据源为自建Hive集群，选择自建Hadoop。
      数据库类型	选择Hive。
      实例名称	E-MapReduce集群ID。 说明 自建Hive集群无此参数。
      网络校验	单击网络校验，提示网络连通。

   2. 单击下一步，填写配置文件。

      参数	说明	获取方式
      参数配置	无	若目标数据源为E-MapReduce集群，支持自动配置获取。 若目标数据源为自建Hive集群需要自行粘贴配置文件信息。
      hdfs-site.xml	HDFS配置项，如副本因子和块大小。	自建Hive集群xml文件一般位于以下两个位置之一： conf目录：在标准安装中，这些配置文件通常放在Hive安装目录下的conf子目录中。 etc/hadoop目录：在集成了Hadoop和Hive的安装中，也可能放在Hadoop的配置目录中，以确保Hive能够正确地与Hadoop集群进行交互。
      core-site.xml	Hadoop核心配置项，如I/O设置和文件系统的配置等。
      yarn-site.xml	YARN配置项，负责集群资源管理和作业调度等。
      mapred-site.xml	用于配置MapReduce作业的参数，如作业执行器和任务优化参数等。
      hive-site.xml	Hive配置项，如元数据存储的连接信息、Hive运行时的内存和性能设置等。
      /etc/host文件	解析Hive集群中每个节点的主机名到IP。	有以下两种获取方式： 方式一：依次登录Hive集群的每台机器，获取/etc/hosts文件中的本节点IP和主机名的映射关系并填写。 方式二：登录Hive namenode所在的机器，运行#!/bin/bash脚本，填写返回结果。返回结果如下： echo $(hostname -i) $(hostname -f) hive dfsadmin -report | awk -F':' '/^Name:/ {printf $2} /^Hostname:/ {print $2}'

   3. 单击保存，并单击左下角完成。

      等待3~5分钟，数据源状态为运行中时，Hive数据源可用。

3. 单击目标数据源操作列的校验。

4. 在服务配置校验窗口中，填写数据集所在Hadoop集群路径（例如：/tmp/file.txt），并单击校验。

   提示配置校验成功后，您可以继续操作读写Hive外表。

配置JDBC数据源

JDBC数据源指兼容MySQL和PostgreSQL的数据源，如RDS MySQL、RDS PostgreSQL、PolarDB MySQL、PolarDB PostgreSQL等。

本文以配置RDS MySQL数据源为例。

前提条件

• 数据源所在实例（RDS MySQL实例）需与AnalyticDB PostgreSQL版实例在同一VPC。

• 已在RDS MySQL实例中创建数据库和账号。具体操作，请参见创建数据库和账号。

• 已将AnalyticDB PostgreSQL版实例的专有网络交换机IP添加至RDS MySQL实例的白名单中。具体操作，请参见设置IP白名单。

  说明

  AnalyticDB PostgreSQL版实例的专有网络交换机IP可在外部数据源管理页面获取。

操作步骤

1. 在异构数据源访问页签，单击新增数据源的下拉菜单，选择JDBC数据源。

2. 在新增JDBC数据源页面页面配置如下参数。

   参数	说明
   数据源名称	可包含英文大小写字母、数字、中划线（-）、下划线（_）。 以数字或者英文字母开始和结尾。 长度不超过50字符。
   数据库类型	选择MySQL。
   数据源描述	数据源描述。
   JDBC连接串	连接RDS MySQL实例的JDBC连接串，格式如下： jdbc:mysql://<servername>:<port>/<dbName> <servername>:<port>：RDS MySQL实例的连接地址和对应的端口。 若通过内网连接，需输入RDS MySQL实例的内网地址和内网端口。 若使用外网连接，需输入RDS MySQL实例的外网地址和外网端口。 您可以在RDS MySQL实例的数据库连接页面查看。 更多信息，请参见查看或修改连接地址和端口。 <dbName>：RDS MySQL实例的数据库。
   用户名	RDS MySQL实例的账号。
   登录密码	账号对应的密码。

3. 单击测试连接，提示网络连通后，单击左下角确定。

   如果网络不通，请检查JDBC连接串地址填写是否正确。

   等待3~5分钟，数据源状态为运行中时，JDBC数据源可用。您可以继续操作读写JDBC外表。

HDFS外表

读写文本数据

语法

CREATE [WRITABLE | READABLE] EXTERNAL TABLE <table_name>
    ( <column_name> <data_type> [, ...] | LIKE <other_table> )
LOCATION ('pxf://<path-to-hdfs-dir>
    ?PROFILE=hdfs:text[&SERVER=<server_name>][&<custom-option>=<value>[...]]')
FORMAT '[TEXT|CSV]' (delimiter[=|<space>][E]'<delim_value>');
[DISTRIBUTED BY (<column_name> [, ... ] ) | DISTRIBUTED RANDOMLY];

参数

示例

• 示例一：创建一个可写外表，在建表语句中指定好HDFS协议、格式及路径，并向表中写入数据。

  CREATE WRITABLE EXTERNAL TABLE pxf_hdfs_writable_table
  ( 
  a int,
  b int,
  c int
  )
  LOCATION ('pxf://data/demo/pxf_hdfs_demo_table?PROFILE=hdfs:text&SERVER=test2')
  FORMAT 'TEXT' (delimiter='|');
  
  INSERT INTO pxf_hdfs_writable_table 
  SELECT generate_series(1, 9000), generate_series(1, 9000), generate_series(1, 9000);

• 示例二：创建一个可读外表，在建表语句中指定好HDFS协议、格式及路径，并查询外表数据。

  CREATE READABLE EXTERNAL TABLE pxf_hdfs_readable_table
  ( 
  a int,
  b int,
  c int
  )
  LOCATION ('pxf://data/demo/pxf_hdfs_demo_table?PROFILE=hdfs:text&SERVER=test2')
  FORMAT 'TEXT' (delimiter='|');
  
  SELECT * FROM pxf_hdfs_readable_table order by 1;

读写Parquet数据

语法

CREATE [WRITABLE |READABLE] EXTERNAL TABLE <table_name>
    ( <column_name> <data_type> [, ...] | LIKE <other_table> )
LOCATION ('pxf://<hdfs_dbName>.<hdfs_tableName>
    ?PROFILE=hdfs:parquet[&SERVER=<server_name>][&<custom-option>=<value>[...]]')
FORMAT 'CUSTOM' (FORMATTER='pxfwritable_import'|'pxfwritable_export');
[DISTRIBUTED BY (<column_name> [, ... ] ) | DISTRIBUTED RANDOMLY];

参数

hdfs:parquet配置文件支持与编码和压缩有关的写入选项，可以在CREATE WRITABLE EXTERNAL TABLE LOCATION子句中指定以下自定义选项：

示例

• 示例一：创建一个可写外表，并在建表语句中指定好HDFS协议、Parquet格式及路径，并向表中写入数据。

  CREATE WRITABLE EXTERNAL TABLE pxf_hdfs_writable_parquet_table
  ( 
  a int,
  b int,
  c int
  )
  LOCATION ('pxf://data/demo/pxf_hdfs_writable_parquet_table?PROFILE=hdfs:parquet&SERVER=test2')
  FORMAT 'CUSTOM' (FORMATTER='pxfwritable_export');
  
  INSERT INTO pxf_hdfs_writable_parquet_table 
  SELECT generate_series(1, 9000), generate_series(1, 9000), generate_series(1, 9000);

• 示例二：创建一个可读外表，并在建表语句中指定好HDFS协议、Parquet格式及路径，并查询表中数据。

  CREATE READABLE EXTERNAL TABLE pxf_hdfs_readable_parquet_table
  ( 
  a int,
  b int,
  c int
  )
  LOCATION ('pxf://data/demo/pxf_hdfs_writable_parquet_table?PROFILE=hdfs:parquet&SERVER=test2')
  FORMAT 'CUSTOM' (FORMATTER='pxfwritable_import');
  
  SELECT * FROM pxf_hdfs_readable_parquet_table;

Hive外表

若需在AnalyticDB PostgreSQL版数据库中展示Hive数据，请将Hive数据值映射到相同类型的AnalyticDB PostgreSQL版数据库列。映射关系，请参见附录4：Hive数据类型与AnalyticDB PostgreSQL数据类型映射关系。

语法

可写Hive外表与可读Hive外表支持的功能有差异，请根据需要选择合适的语法。

• 可写外表

  由于在新版本中Hive相关数据写入接口进行了较大变更，与早期版本的底层逻辑不同，可能在使用新版本Hive外表进行创建或写入数据时报错，建议使用HDFS方式创建外表进行写入。语法详情请参见HDFS外表。

• 可读外表

  CREATE [READABLE] EXTERNAL TABLE <table_name>
      ( <column_name> <data_type> [, ...] | LIKE <other_table> )
  LOCATION ('pxf://<hive-db-name>.<hive-table-name>
      ?PROFILE=Hive|HiveText|HiveRC|HiveORC|HiveVectorizedORC[&SERVER=<server_name>]'])
  FORMAT 'CUSTOM|TEXT' (FORMATTER='pxfwritable_import' | delimiter='<delim>')

参数

示例

1. 登录Hive集群，创建Hive表。

   hive> CREATE EXTERNAL TABLE IF NOT EXISTS pxf_hive_demo_table
   (
   a int,
   b int,
   c int
   )
   row format delimited fields terminated by '|'
   stored as textfile
   location '/data/demo/pxf_hdfs_demo_table';

2. 登录AnalyticDB PostgreSQL版数据库，创建可写Hive外表，并向外表插入数据。

   postgres> CREATE WRITABLE EXTERNAL TABLE pxf_hive_writable_table
   ( 
   a int,
   b int,
   c int
   )
   LOCATION ('pxf://default.pxf_hive_demo_table?PROFILE=Hive&SERVER=test2')
   FORMAT 'TEXT' (delimiter='|');
   
   INSERT INTO pxf_hive_writable_table 
   SELECT generate_series(1, 9000), generate_series(1, 9000), generate_series(1, 9000);

3. 创建可读Hive外表，并查询外表数据。

   postgres> CREATE EXTERNAL TABLE pxf_hive_readable_table
   ( 
   a int,
   b int,
   c int
   )
   LOCATION ('pxf://default.pxf_hive_demo_table?PROFILE=Hive&SERVER=test2')
   FORMAT 'TEXT' (delimiter='|');
   
   SELECT * FROM pxf_hive_readable_table;

JDBC外表

支持的数据类型

JDBC连接器支持以下数据类型：

• INTEGER，BIGINT，SMALLINT

• REAL，FLOAT8

• NUMERIC

• BOOLEAN

• VARCHAR，BPCHAR，TEXT

• DATE

• TIMESTAMP

• BYTEA

语法

使用以下语法创建外表并使用JDBC连接器读取或写入数据，如需访问远程SQL数据库中的数据，可以创建一个引用该远程数据库表的可读或可写的

外表。

外表、远程数据库表和查询结果元组必须具有相同的列名称和列类型。

CREATE [READABLE | WRITABLE] EXTERNAL TABLE <table_name>
    ( <column_name> <data_type> [, ...] | LIKE <other_table> )
LOCATION ('pxf://<external_tableName>?PROFILE=Jdbc[&SERVER=<server_name>][&<custom-option>=<value>[...]]')
FORMAT 'CUSTOM' (FORMATTER='pxfwritable_import'|'pxfwritable_export');

参数

JDBC自定义选项

您可以在LOCATION URI中添加JDBC连接器自定义选项，并在每个选项前加上&符号。JDBC支持的custom-option包括：

&PARTITION_BY=id:int&RANGE=1:100&INTERVAL=5
&PARTITION_BY=year:int&RANGE=2011:2013&INTERVAL=1
&PARTITION_BY=createdate:date&RANGE=2013-01-01:2016-01-01&INTERVAL=1:month
&PARTITION_BY=color:enum&RANGE=red:yellow:blue

示例

1. 在MySQL或PostgreSQL数据库中，创建数据表，并插入数据。

CREATE TABLE test(id int, name VARCHAR(255));
INSERT INTO test(id,name) VALUES(1,'qingzhi');
INSERT INTO test(id,name) VALUES(2,'jianli');

2. 登录AnalyticDB PostgreSQL版数据库，创建JDBC外表，并查询外表数据。

CREATE EXTERNAL TABLE pxf_jdbc_textsimple(id int, name varchar)
LOCATION ('pxf://test?PROFILE=Jdbc&SERVER=test01')
FORMAT 'CUSTOM' (FORMATTER='pxfwritable_import')
ENCODING 'UTF8';
SELECT * FROM pxf_jdbc_textsimple;